package ar.edu.unlu.sistemas.p2p.android.sql;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import ar.edu.unlu.sistemas.p2p.business.util.Constants;

	
	public class IndexOpenHelper extends SQLiteOpenHelper {
		
		private SQLiteDatabase db;
		private static final int DATABASE_VERSION = 2;
		private static final String DATABASE_NAME = "index";
		public static final String WORD_ID = "id";
		public static final String WORD_KEY = "word";
	    public static final String WORD_TABLE_NAME = "word";
	    private static final String WORD_TABLE_CREATE =
	                "CREATE TABLE IF NOT EXISTS " + WORD_TABLE_NAME + " (" +
	                WORD_ID + " INTEGER NOT NULL PRIMARY KEY, "+
	                WORD_KEY + " TEXT NOT NULL UNIQUE);";
	    public static final String DOC_ID = "id";
		public static final String DOC_KEY = "doc_name";
		public static final String DOC_TYPE = "doc_type";
		public static final String DOC_TABLE_NAME = "doc";
	    private static final String DOC_TABLE_CREATE =
	                "CREATE TABLE IF NOT EXISTS " + DOC_TABLE_NAME + " (" +
	                DOC_ID + " INTEGER PRIMARY KEY, "+
	                DOC_KEY + " TEXT NOT NULL UNIQUE, "+
	                DOC_TYPE + " TEXT NOT NULL);";
	    public static final String TF_DOC = "doc_id";
		public static final String TF_WORD = "word_id";
		public static final String TF_VALUE = "value";
	    public static final String TF_TABLE_NAME = "tf";
	    private static final String TF_TABLE_CREATE =
	                "CREATE TABLE IF NOT EXISTS " + TF_TABLE_NAME + " (" +
	                TF_DOC + " INTEGER NOT NULL, " +
	                TF_WORD + " INTEGER NOT NULL, " +
	                TF_VALUE + " FLOAT NOT NULL," +
	                "CONSTRAINT tf_uk UNIQUE("+TF_DOC+","+TF_WORD+"));";
	    
	    public static final String QP_VIEW_NAME = "query_process";
	    private static final String QP_VIEW_CREATE = 
	    			"CREATE VIEW IF NOT EXISTS "+ QP_VIEW_NAME +
	    			" AS SELECT qtf."+TF_DOC+" qry_id, dtf."+TF_DOC+" doc_id,SUM(dtf."+TF_VALUE+"*qtf."+TF_VALUE+") sum_ac "+
	    			"FROM "+TF_TABLE_NAME+" qtf INNER JOIN "+TF_TABLE_NAME+" dtf ON qtf."+TF_WORD+" = dtf."+TF_WORD+" "+
	    			"INNER JOIN "+DOC_TABLE_NAME+" d ON dtf."+TF_DOC+" = d."+DOC_ID+" "+
	    			"INNER JOIN "+DOC_TABLE_NAME+" q ON qtf."+TF_DOC+" = q."+DOC_ID+" "+
	    			"WHERE q."+DOC_TYPE+" = '"+Constants.QUERY_TYPE+"' "+
	    			"AND d."+DOC_TYPE+" = '"+Constants.DOC_TYPE+"' "+
	    			"AND qtf."+TF_WORD+" != (SELECT id FROM "+WORD_TABLE_NAME+" WHERE "+WORD_KEY+" = '"+Constants.SQUARE_SUM_WORD+"') "+
	    			"GROUP BY qtf."+TF_DOC+", dtf."+TF_DOC+";";
	    public static final String WORD_F_ID = "word_id";
		public static final String WORD_F_VALUE = "word_value";
	    public static final String WORD_F_TABLE_NAME = "word_f";
	    private static final String WORD_F_TABLE_CREATE =
	                "CREATE TABLE IF NOT EXISTS " + WORD_F_TABLE_NAME + " (" +
	                WORD_F_ID + " INTEGER NOT NULL PRIMARY KEY, "+
	                WORD_F_VALUE + " INTEGER NOT NULL);";

	    public SQLiteDatabase getDb() {
			return db;
		}

		public void setDb(SQLiteDatabase db) {
			this.db = db;
		}
	    
	    public IndexOpenHelper(Context context) {
	        super(context,DATABASE_NAME, null, DATABASE_VERSION);
	        Log.i(this.getClass().getSimpleName(),"Creo la BD "+DATABASE_NAME);
	        this.onCreate(this.getWritableDatabase());
	    }

	    @Override
	    public void onCreate(SQLiteDatabase db) {
	    	this.db = db;
	    	Log.i("Ejecuta SQL",WORD_TABLE_CREATE);
	        db.execSQL(WORD_TABLE_CREATE);
	        Log.i("Ejecuta SQL",DOC_TABLE_CREATE);
	        db.execSQL(DOC_TABLE_CREATE);
	        Log.i("Ejecuta SQL",TF_TABLE_CREATE);
	        db.execSQL(TF_TABLE_CREATE);
	        Log.i("Ejecuta SQL",QP_VIEW_CREATE);
	        db.execSQL(QP_VIEW_CREATE);
	        Log.i("Ejecuta SQL",WORD_F_TABLE_CREATE);
	        db.execSQL(WORD_F_TABLE_CREATE);
	    }
	    
	    @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	    	db.execSQL("DROP TABLE IF EXISTS " + QP_VIEW_NAME);
	    	db.execSQL("DROP TABLE IF EXISTS " + TF_TABLE_NAME);
            db.execSQL("DROP TABLE IF EXISTS " + WORD_TABLE_NAME);
            db.execSQL("DROP TABLE IF EXISTS " + DOC_TABLE_NAME);
            db.execSQL("DROP TABLE IF EXISTS " + WORD_F_TABLE_NAME);
            onCreate(db);
        }
	    
	    
	}

